package com.ruoyi.project.rate;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.ruoyi.project.rate.domain.Rule;

/**
 * Excel相关处理
 * 
 * @author ruoyi
 */
public class ExcelUtil {
	private Workbook wb;
	private Sheet sheet;
	
    public List<Rule> importRule(InputStream is) throws Exception {
        this.wb = WorkbookFactory.create(is);
        List<Rule> list = new ArrayList<>();
        this.sheet = wb.getSheetAt(0);
        if (this.sheet == null) {
            throw new IOException("文件sheet不存在");
        }
        
        int rows = sheet.getPhysicalNumberOfRows();
        Row row;
        if (rows > 1) {
            for (int i = 1; i < rows; i++) {
                // 从第2行开始取数据,默认第一行是表头.
                row = sheet.getRow(i);
                int colIndex = 0; // 从第一列开始
                Rule rule = new Rule();
                rule.getDept().setDeptName(getCellValueToString(row.getCell(colIndex++)));
                rule.setXm(getCellValueToString(row.getCell(colIndex++)));
                rule.setXh(getCellValueToString(row.getCell(colIndex++)));
                rule.setFz(getCellValueToString(row.getCell(colIndex++)));
                rule.setNr(getCellValueToString(row.getCell(colIndex++)));
                rule.setFs(getCellValueToString(row.getCell(colIndex++)));
                
                list.add(rule);
            }
        }
        return list;
    }
    
    private String getCellValueToString(Cell cell) {
        if (cell == null) {
        	return null;
        }
        Object val = null;
        if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA) {
            val = cell.getNumericCellValue();
            if (HSSFDateUtil.isCellDateFormatted(cell))
            {
                val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
            }
            else
            {
                if ((Double) val % 1 > 0)
                {
//                    val = new DecimalFormat("0.00").format(val);
                }
                else
                {
                    val = new DecimalFormat("0").format(val);
                }
            }
        } else if (cell.getCellTypeEnum() == CellType.STRING) {
            val = cell.getStringCellValue();
        } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
            val = cell.getBooleanCellValue();
        } else if (cell.getCellTypeEnum() == CellType.ERROR) {
            val = cell.getErrorCellValue();
        }
        
        if (val == null) {
        	return null;
        }
        return val.toString().trim();
    }
    
    public void destroy() {
    }
}